﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using ReBasic.Model;

namespace ReBasic.Helper
{
    public class ProBaseHeper
    {
        SqlConnection sqlcon;
        SqlCommand Cmd;
        SqlDataAdapter Da;
        DataSet Ds;
        DataTable Dt;

        public bool DataBind()
        {
            sqlcon = new SqlConnection();
            string strConn =
                "Data Source=DELL-PC;" +
                "Initial Catalog=ProBase;" +
                "Integrated Security=True";
            try
            {
                sqlcon.ConnectionString = strConn;
                sqlcon.Open();
            }
            catch
            {
                return false;
            }
            return true;
        }

        public bool Terminate()
        {
            try
            {
                sqlcon.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        /// <summary>
        /// 运行无返回值的sql操作
        /// </summary>
        /// <param name="cmdInfo">sql语句</param>
        public void RunAction(string cmdInfo)
        {
            Cmd = new SqlCommand(cmdInfo, sqlcon);
            Cmd.CommandType = CommandType.Text;
            Cmd.ExecuteNonQuery();
        }

        public List<Relation> RunRelations(string cmdInfo)
        {
            Cmd = new SqlCommand(cmdInfo, sqlcon);
            Cmd.CommandType = CommandType.Text;
            Da = new SqlDataAdapter(Cmd);
            Ds = new DataSet();
            Da.Fill(Ds, "demo");
            List<Relation> res = new List<Relation>();
            Dt = Ds.Tables[0];
            for (int i = 0; i < Dt.Rows.Count; i++)
            {
                Relation rel = new Relation();
                rel.SuperConcept = Dt.Rows[i]["SuperConcept"].ToString();
                rel.SubConcept = Dt.Rows[i]["SubConcept"].ToString();
                rel.Frequency = Convert.ToInt32(Dt.Rows[i]["Frequency"]);
                res.Add(rel);
            }
            return res;
        }

        public int RunFrequency(string cmdInfo)
        {
            Cmd = new SqlCommand(cmdInfo, sqlcon);
            Cmd.CommandType = CommandType.Text;
            Da = new SqlDataAdapter(Cmd);
            Ds = new DataSet();
            Da.Fill(Ds, "demo");
            int res = Convert.ToInt32(Ds.Tables[0].Rows[0][0]);
            return res;
        }

        public void AddSent(string sent)
        {
            string cmd =
                "INSERT INTO Sentences VALUES ('" + sent + "')";
            RunAction(cmd);
        }

        public void AddConcept(string sup, string sub, string freq)
        {
            string cmd =
                "INSERT INTO Concepts VALUES ('" +
                sup + "', '" + sub + "', " + freq + ")";
            RunAction(cmd);
        }

        public List<Relation> FindSuperConcept(string sup, int top)
        {
            sup = sup.Replace("'", "''");
            string cmd;
            if (top == 0)
                cmd = "SELECT * FROM Concepts WHERE SuperConcept = '" 
                    + sup + "' ORDER BY Frequency DESC";
            else
                cmd = "SELECT TOP " + top.ToString() 
                    + " * FROM Concepts WHERE SuperConcept = '" 
                    + sup + "' ORDER BY Frequency DESC";
            return RunRelations(cmd);
        }

        public int FindSuperCount(string sup, bool weight)
        {
            sup = sup.Replace("'", "''");
            string cmd;
            if (weight == true)
                cmd = "SELECT sum(Frequency) FROM Concepts WHERE SuperConcept = '" + sup + "'";
            else
                cmd = "SELECT count(*) FROM Concepts WHERE SuperConcept = '" + sup + "'";
            return RunFrequency(cmd);
        }

        public List<Relation> FindSubConcept(string sub, int top)
        {
            sub = sub.Replace("'", "''");
            string cmd;
            if (top == 0)
                cmd = "SELECT * FROM Concepts WHERE SubConcept = '"
                    + sub + "' ORDER BY Frequency DESC";
            else
                cmd = "SELECT TOP " + top.ToString()
                    + " * FROM Concepts WHERE SubConcept = '"
                    + sub + "' ORDER BY Frequency DESC";
            return RunRelations(cmd);
        }

        public int FindSubCount(string sub, bool weight)
        {
            sub = sub.Replace("'", "''");
            string cmd;
            if (weight == true)
                cmd = "SELECT sum(Frequency) FROM Concepts WHERE SubConcept = '" + sub + "'";
            else
                cmd = "SELECT count(*) FROM Concepts WHERE SubConcept = '" + sub + "'";
            return RunFrequency(cmd);
        }
    }
}
